<?php
class ProfileController extends AdminController {

	const MODEL_CLASS_NAME = 'Profile';

	public function actionIndex() {
		$model = new Profile(Profile::SCENARIO_SEARCH);
		$model->unsetAttributes();  // clear any default values
		if($model->family == null){
			$model->family = new Family();
		}
		$model->family->unsetAttributes();
		if($model->advanceProfile == null){
			$model->advanceProfile = new AdvanceProfile();
			$model->advanceProfile->hiringCompany = new HiringCompany();
		}
		$model->advanceProfile->unsetAttributes();
		$model->advanceProfile->hiringCompany->unsetAttributes();
		if (isset($_GET[ProfileController::MODEL_CLASS_NAME])) {
			$model->attributes = $_GET[ProfileController::MODEL_CLASS_NAME];
			$model->family->attributes = $_GET['Family'];
			$model->advanceProfile->attributes = $_GET['AdvanceProfile'];
			$model->advanceProfile->hiringCompany->attributes = $_GET['HiringCompany'];
		}

		$this->render('index', array('model' => $model));
	}

	function actions() {
		return array(
	    'delete' => array(
			'class' => 'lib.components.actions.DeleteAction',
	        'ajaxView' => "//share/_delete",
	        'customAction' => 'deleteProfile',
			'messages' => array('success' => t('MSG_INFO_DELETE_SUCCESS', 'messages', array('{0}' => t('LBL_PROFILE','profile')))),		
			),
		);
	}

	public function loadModel() {
		$model = null;
		if (isset($_GET['id'])) {
			$ids = explode(',', $_GET['id']);
			if (isset($ids)) {
				$criteria = new CDbCriteria;
				$criteria->addInCondition('t.id', $ids);
				$model = Profile::model()->with('family');
				$model = Profile::model()->with('workingProcesses');
				$model = Profile::model()->with(array('academy' => array('with' => array('studyingProcesses'))))->findAll($criteria);
			}
		}
		return $model;
	}
	
	public function actionDetail($id){
		$model = Profile::model()->findByPk($id);
		$this->render('view', array(
              'model' => $model));
	}

	public function actionUpdate($id){
		$model = Profile::model()->findByPk($id);
		
		if (isset($_POST[ProfileController::MODEL_CLASS_NAME])) {
			//Keep track model before update to save log.
			$oldModel = Profile::model()->findByPk($id);
			// Format date to the same format with new profile
			$date = DateTime::createFromFormat ( 'd-m-Y', $oldModel->birth_date );
			$oldModel->birth_date = $date->format ('d-m-Y');
			
			// Get input data
			$model->attributes = $_POST[ProfileController::MODEL_CLASS_NAME];
			$fileUpload = $_FILES[ProfileController::MODEL_CLASS_NAME];
			$oldPicture = $model->photo_url;
			if ($fileUpload['size']['picture'] > 0) {
				$newName = gmdate('YmdGis') . '_' . stringSafeFile($fileUpload['name']['picture']);
				$model->photo_url = $newName;
			}
			//Update status if user submit
			if(isset($_POST["cmsTask"]) && $_POST["cmsTask"] == ActionType::SUBMIT){
				$model->status = ProfileStatus::STATUS_WAITING;
			}

			$model->family->attributes = $_POST['Family'];
			$model->academy->attributes = $_POST['Academy'];
			$model->scenario = 'update';
					
			//Get translated Model to update translated studying process and  working processes and keep track updated fields
			$translatedModel = $model->get_translated_model();
			
			// Keep track updated fields.
			if ($translatedModel != null && count($translatedModel) > 0){
				$model->updated_fields = $this->get_changed_attributes($oldModel);
			}
			
			// Save only if model validates
			if ($model->validate()) {
				// Save the model
				if ($model->save(true)) {
					if ($fileUpload['size']['picture'] > 0) {
						move_uploaded_file($fileUpload['tmp_name']['picture'], PROFILE_FOLDER . $newName);
					}
					$model->family->profile_id = $id;
					$model->academy->profile_id = $model->id;
					if ($model->family->validate()&& $model->academy->validate()){
						$model->family->save();
						//Check and save academy
						if($this->isAllAttributesEmpty($_POST['Academy']) == FALSE || (isset($_POST['StudyingProcess']) && $this->isAllAttributesEmpty($_POST['StudyingProcess']) == FALSE)){
							$model->academy->save();
							//Create new academy for translated model if we add academy or studying process while we updat the vn profile.
							if ($translatedModel != null && count($translatedModel) > 0 && $this->isAllAttributesEmpty($translatedModel[0]->academy->attributes)){
								$translatedModel[0]->academy = new Academy();
								$translatedModel[0]->academy->unsetAttributes();
								$translatedModel[0]->academy->profile_id = $translatedModel[0]->id;
								$translatedModel[0]->academy->save();
							}
						}
						
						
						
						//Update old studyprocesses
                        foreach($model->academy->studyingProcesses as $i => $process){
                           if(isset($_POST['StudyingProcess']) && isset($_POST['StudyingProcess'][$i])){
							   $changed_fields = $this->get_changed_studyingprocess_attributes($process, $_POST['StudyingProcess'][$i]);	                           	
                               $process->attributes = $_POST['StudyingProcess'][$i];
                               $process->updated_fields = $changed_fields;
                               $process->academy_id = $model->academy->id;
                               if ($this->isAllAttributesEmpty($_POST['StudyingProcess'][$i])==FALSE){
                               		$process->save();
                               }else if($process->isNewRecord == FALSE){
									$process->save();
                               }
                           }else if($process->isNewRecord == FALSE){
                           	   StudyingProcess::model()->deleteAll(array('condition'=>"related_id = :related_id", 'params'=>array(':related_id' => $process->id)));
                               $process->delete();                                                               
                           }                                                       
                        }       
						//check and add new studyprocesses
                        if(isset($_POST['StudyingProcess'])){
                            foreach($_POST['StudyingProcess'] as $j => $submittedprocess){                            
                                if(array_key_exists($j,$model->academy->studyingProcesses) == FALSE){
	                                $studyingProcesses = new StudyingProcess('create');
	                                $studyingProcesses->academy_id = $model->academy->id;
	                                $studyingProcesses->attributes = $submittedprocess;
                                    if ($this->isAllAttributesEmpty($submittedprocess)==FALSE && $studyingProcesses->validate()){                                        
                                    	$studyingProcesses->language = ProfileLanguage::VN;
                                    	$studyingProcesses->save();
                                		
                                    	if($translatedModel != null && count($translatedModel) > 0){
	                                		$studyingProcessesJP = new StudyingProcess();
	                                    	$studyingProcessesJP->academy_id = $translatedModel[0]->academy->id;
	                                    	$studyingProcessesJP->language = ProfileLanguage::JP;
	                                    	$studyingProcessesJP->related_id = $studyingProcesses->id;
	                                    	$studyingProcessesJP->save();
                                    	}
                                    }                               
                                }
                            }   
                        }

						//Update working process                        
                        foreach($model->workingProcesses as $m => $work){
                           if(isset($_POST['WorkingProcess']) && isset($_POST['WorkingProcess'][$m])){
                           	   $changed_fields = $this->get_changed_workingprocess_attributes($work, $_POST['WorkingProcess'][$m]);
                               $work->attributes = $_POST['WorkingProcess'][$m];
                               $work->updated_fields = $changed_fields;
                               $work->profile_id = $model->id;
                               if ($this->isAllAttributesEmpty($_POST['WorkingProcess'][$m])==FALSE){
									$work->save();
                               }else if($work->isNewRecord == FALSE){
                               		$work->save();
                               }
                           }else if($work->isNewRecord == FALSE){
                           		WorkingProcess::model()->deleteAll(array('condition'=>"related_id = :related_id", 'params'=>array(':related_id' => $work->id)));
                            	$work->delete();                                                               
                           }                                                       
                        }            
                        //check and add new workingprocesses
                        if(isset($_POST['WorkingProcess'])){
                            foreach($_POST['WorkingProcess'] as $n => $submittedwork){                            
                                if(array_key_exists($n,$model->workingProcesses) == FALSE){
	                                $workingProcess = new WorkingProcess('create');
	                                $workingProcess->profile_id = $model->id;
	                                $workingProcess->attributes = $submittedwork;
                                    if ($this->isAllAttributesEmpty($submittedwork)==FALSE && $workingProcess->validate()){                                        
                                    	$workingProcess->language = ProfileLanguage::VN;
                                    	$workingProcess->save();
                                        
                                    	if($translatedModel != null && count($translatedModel) > 0){
	                                        $workingProcessJP = new WorkingProcess();
	                                        $workingProcessJP->profile_id = $translatedModel[0]->id;
	                                        $workingProcessJP->language = ProfileLanguage::JP;
	                                        $workingProcessJP->related_id = $workingProcess->id;
	                                        $workingProcessJP->save();
                                    	}
                                    }                               
                                }
                            }   
                        } 
                        //Save history to database
                        $dataLogger = new DataLogger();
                        $dataLogger->recordProfileChanges($oldModel, $model);
                        
                        //Render data to page
                        $this->showMessage(MsgType::SUCCESS, t('MSG_INFO_UPDATE_SUCCESS', 'messages', array('{0}' => t('LBL_PROFILE','profile'))));                        
                        if (isset($_POST["cmsTask"])) {
                            $cmsTask = $_POST["cmsTask"];
                            switch ($cmsTask) {
                                case ActionType::SAVE: {                                       
                                        $url = url($this->id . '/update', array('id' => $model->id));
                                        $this->redirect($url);                                        
                                    break;
                                    }
                                case ActionType::SAVE_CLOSE: {
                                        $this->redirect(url($this->id));
                                    break;
                                    }
                                case ActionType::SAVE_NEW: {
                                        $this->redirect(url(app()->controller->id . '/create'));
                                    break;
                                }  
                                case ActionType::SUBMIT: {
                                    $this->redirect(url($this->id));                                        
                                    break;
                                }
                                case ActionType::SAVE_CREATE_ADVANCE: {
                                	$this->redirect(url('advanceprofile/index/', array('id'=>$model->id)));
                                	break;
                                }
                            }
                        }                                    
                    }                
                }
            }else{
                $model->photo_url = $oldPicture;
            }
        }
        
        $this->render('update', array(
		'model' => $model,
		'cancelUrl' => array('view', 'id' => $id)));
	}

	public function deleteProfile($id){
		$model = $this->loadModel();		
		//Must throw CHttpException, If not, when ajax request, error will not run errorHandler
		if ($model == NULL || count($model) == 0) {
			//Must return 200 to client recieve this error
			throw new CHttpException(200, t('THERE_WAS_AN_ERROR_WHILE_DELETING._PLEASE_TRY_AGAIN.', 'messages'));
		}
		foreach($model as $i => $item){
			$item->is_deleted = 1;
			$item->saveAttributes(array('is_deleted'));			
		}
		return new LGMessage(MsgType::SUCCESS, t('MSG_INFO_DELETE_SUCCESS', 'messages', array('{0}' => t('LBL_PROFILE','profile'))));
	}

	public function actionCreate() {
		$model = new Profile('create');
		$model->family = new Family('create');
		$model->academy = new Academy('create');
		if (method_exists($model, 'unsetAttributes')) {
			$model->unsetAttributes();
		}
		if (method_exists($model->family, 'unsetAttributes')) {
			$model->family->unsetAttributes();
		}
		if (method_exists($model->academy, 'unsetAttributes')) {
			$model->academy->unsetAttributes();
		}
		if ($model->academy !=null) {
			$tmp = $model->academy->studyingProcesses;
			$process = new StudyingProcess();
			$process->unsetAttributes();
			//$tmp[] = $process;
			//$model->academy->studyingProcesses = $tmp; //Create a new item
		}

		if ($model !=null) {
			$workTmp = $model->workingProcesses;
			$work = new WorkingProcess();
			$work->unsetAttributes();
			//$workTmp[] = $work;
			//$model->workingProcesses = $workTmp; //Create a new item
		}

		if (isset($_POST[ProfileController::MODEL_CLASS_NAME])) {
			$model->attributes = $_POST[ProfileController::MODEL_CLASS_NAME];
			$model->language = ProfileLanguage::VN;
			$fileUpload = $_FILES[ProfileController::MODEL_CLASS_NAME];
			$oldPicture = $model->photo_url;
			if ($fileUpload['size']['picture'] > 0) {
				$newName = gmdate('YmdGis') . '_' . stringSafeFile($fileUpload['name']['picture']);
				$model->photo_url = $newName;
			}
			//Update status if user submit
			if(isset($_POST["cmsTask"]) && ($_POST["cmsTask"] == ActionType::SUBMIT || $_POST["cmsTask"] == ActionType::SAVE_CREATE_ADVANCE))
			{
				$model->status = ProfileStatus::STATUS_WAITING;
			}
			// Save only if model validates
			if ($model->validate()) {
				if ($model->save(true)) {
					if ($fileUpload['size']['picture'] > 0) {
						move_uploaded_file($fileUpload['tmp_name']['picture'], PROFILE_FOLDER . $newName);
					}
					$model->family->profile_id = $model->id;
					$model->academy->profile_id = $model->id;
					$model->family->attributes = $_POST['Family'];
					$model->academy->attributes = $_POST['Academy'];
					// If callback is set run additional processing
					if ($model->family->validate() && $model->academy->validate()){
						$model->family->save(true);
						//Check and save academy
						if($this->isAllAttributesEmpty($_POST['Academy'])==FALSE || (isset($_POST['StudyingProcess']) && $this->isAllAttributesEmpty($_POST['StudyingProcess'])==FALSE)){
							$model->academy->save(true);
						}
						
						if (isset($_POST['StudyingProcess']) && $this->isAllAttributesEmpty($_POST['StudyingProcess'])==FALSE){
						    //check and add new studyprocesses
	                        foreach($_POST['StudyingProcess'] as $j => $submittedprocess){                                                        
	                            $studyingProcesses = new StudyingProcess('create');
	                            $studyingProcesses->academy_id = $model->academy->id;
	                            $studyingProcesses->attributes = $submittedprocess;
	                            if ($this->isAllAttributesEmpty($submittedprocess)==FALSE && $studyingProcesses->validate()){
	                            	$studyingProcesses->language = ProfileLanguage::VN;
	                            	$studyingProcesses->save();
	                            }                                                           
	                        }
						}
                                                                        
                        //check and add new workingprocesses
                        if(isset($_POST['WorkingProcess'])){
                            foreach($_POST['WorkingProcess'] as $n => $submittedwork){                                                            
                                $workingProcess = new WorkingProcess('create');
                                $workingProcess->profile_id = $model->id;
                                $workingProcess->attributes = $submittedwork;
                                if ($this->isAllAttributesEmpty($submittedwork)==FALSE && $workingProcess->validate()){                                        
                                	$workingProcess->language = ProfileLanguage::VN;
                                	$workingProcess->save();
                                }                                                               
                            }   
                        } 
						$this->showMessage(MsgType::SUCCESS, t('MSG_INFO_INSERT_SUCCESS', 'messages' , array('{0}' => t('LBL_PROFILE','profile'))));
						if (isset($_POST["cmsTask"])) {
							$cmsTask = $_POST["cmsTask"];
							switch ($cmsTask) {
								case ActionType::SAVE: {
									$url = url($this->id . '/update', array('id' => $model->id));
									$this->redirect($url);
									break;
								}
								case ActionType::SAVE_CLOSE: {
									$this->redirect(url($this->id));
									break;
								}
								case ActionType::SAVE_NEW: {
									$this->redirect(url(app()->controller->id . '/create'));
									break;
								}
								case ActionType::SUBMIT: {
									$this->redirect(url($this->id));
									break;
								}
								case ActionType::SAVE_CREATE_ADVANCE: {
									$this->redirect(url('advanceprofile/index/', array('id'=>$model->id)));
									break;
								}
							}
						}
						app()->end();
					}
				}
			}else{
				$model->photo_url = $oldPicture;
			}
		}

		$this->render('create', array(
		'model' => $model,
		'cancelUrl' => array('admin')));               	       
	}

	public function actionReject($id)
	{
		$this->_changeStatus($id, ProfileStatus::STATUS_REJECTED);
	}
	
	public function _changeStatus($id, $status) {
		$model = Profile::model()->findByPk($id);
	    $model->status = $status;
	    $model->scenario = Profile::SCENARIO_UPDATE;	    
	    $isSuccess = $model->saveAttributes(array('status'));
	    if($isSuccess)
	    {
	    	$this->showMessage(MsgType::SUCCESS, t('MSG_INFO_INSERT_SUCCESS', 'messages' , array('{0}' => t('LBL_PROFILE','profile'))));	    	
	    }
	    else 
	    {
	    	$this->showMessage(MsgType::ERROR, t('MSG_ERROR_UPDATE_UNSUCCESS', 'messages' , array('{0}' => t('LBL_PROFILE','profile'))));
	    }
	    $this->redirect(url($this->id));
    }

	public function actionLoadStudyingProcessByAjax($index)
	{
		$studyingprocess = new StudyingProcess();
		$this->renderPartial('/studyingprocess/_form', array(
            'model' => $studyingprocess,
            'index' => $index,
            'display' => 'block',
		), false, true);
	}

	public function actionLoadWorkingProcessByAjax($index)
	{
		$workingProcess = new WorkingProcess();
		$this->renderPartial('/workingprocess/_form', array(
            'model' => $workingProcess,
            'index' => $index,
            'display' => 'block',
		), false, true);
	}

	public function actionRemovePictureByAjax($index)
	{
		$model = Profile::model()->findByPk($index);
		$fileName = $model->photo_url;
		$model->photo_url = null;
		if($model->save()){
			unlink(PROFILE_FOLDER . $fileName);
		}
	}

	public function isAllAttributesEmpty($attributes){
		foreach($attributes as $att){
			if ((is_array($att)) || ($att instanceof Traversable))
			{
				return $this->isAllAttributesEmpty($att);
			}else{
				if($att != ''){
					return FALSE;
				}
			}
		}
		return TRUE;
	}
	
	public function actionPrint($id)
	{
		$model = Profile::model()->findByPk($id);
		$this->layout = 'print';
		$this->render('print', array(
              'model' => $model));
	}
	
	public function actionExport() {
		
		//Prepare data
		$row_index = 1;
		$model = new Profile(Profile::SCENARIO_EXPORT);
		$model->academy = new Academy();
		$model->province = new Province();
		$ids = $_GET['id'];
		$requestor_list = $model->export($ids);
		$total_requestor = count($requestor_list);
		
		ini_set("memory_limit", "-1");
		/* Fill data to Excel */
		Yii::import('ext.PHPExcel.**', true);
		$logedInUser = User::model()->findByPk(Yii::app()->user->id);
		$objPHPExcel = new PHPExcel();
		//Set default style
		$default_style = array(
				'font' => array(
						'name' => 'Calibri',
						'size' => 11,
						'color' => array('rgb' => '333333'),
				),
				'alignment' => array('vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER),
		);
		$objPHPExcel->getDefaultStyle()->applyFromArray($default_style);
		$objPHPExcel->setActiveSheetIndex(0);
		$active_sheet = $objPHPExcel->getActiveSheet();
		
		//page setup
		$active_sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
		$active_sheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
		$active_sheet->getPageSetup()->setFitToPage(true);
		$active_sheet->getPageSetup()->setFitToWidth(1);
		$active_sheet->getPageSetup()->setFitToHeight(0);
		$pageMargins = $active_sheet->getPageMargins();
		// margin is set in inches (0.5cm)
		$margin = 1 / 2.54;
		$pageMargins->setTop($margin);
		$pageMargins->setBottom($margin);
		$pageMargins->setLeft($margin);
		$pageMargins->setRight($margin);
		
		$active_sheet->setTitle(t('EXPORT_SHEET_TITLE', 'profile'))
		->setShowGridlines(false);
		$active_sheet->getColumnDimension("A")->setWidth(10);
		$active_sheet->getColumnDimension("B")->setWidth(30);
		$active_sheet->getColumnDimension("C")->setWidth(13);
		$active_sheet->getColumnDimension("D")->setWidth(20);
		$active_sheet->getColumnDimension("E")->setWidth(10);
		$active_sheet->getColumnDimension("F")->setWidth(15);
		$active_sheet->getColumnDimension("G")->setWidth(20);
		
		
		#Insert image into cell
		$objDrawing = new PHPExcel_Worksheet_Drawing();
		$objDrawing->setWorksheet($active_sheet);
		$objDrawing->setName("logo");
		$objDrawing->setDescription("lotus-ocean logo");
		$objDrawing->setPath(PROFILE_FOLDER . "vinaconex.png");
		$objDrawing->setCoordinates('A1');
		$objDrawing->setHeight(50);
		$objDrawing->setOffsetX(30);
		$objDrawing->setOffsetY(5);
		//$active_sheet->getRowDimension($row_index)->setRowHeight(50);		
		
		//Contact info
		$contactInfoStyleArray = array(
				'font' => array(
						'size' => 9,
				),
				'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER),
		);
		
		$contact_info_range = "E1:G5";
		$active_sheet->setCellValue('E1', "本社：47 DIEN BIEN PHU STREER,DAKAO WARD,DISTRICT 1, HCMC.\n教育センター：39　TRUONG CONG DINH STREET WARD 14\n TAN BINH DISTRICT, HCM CITY\nTel: +84 3811 2689　　Fax: +84 6268 8555\nWebsite : www.vcsg.vn")
		->mergeCells($contact_info_range)			
		->getStyle($contact_info_range)->applyFromArray($contactInfoStyleArray);
		$active_sheet->getStyle($contact_info_range)->getAlignment()->setWrapText(true);

		//title
		$titleStyleArray = array(
				'font' => array(
						'bold' => true,
						'size' => 16,
						'color' => array('rgb' => 'FFFFFF'),
				),
				'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,
						'color' => array('rgb' => '004477')),
				'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER),
		);
		
		$title_range = "C7:F7";
		$active_sheet->setCellValue('C7', "候補者名簿")
					 ->mergeCells($title_range)
					 ->getStyle($title_range)->applyFromArray($titleStyleArray);
		$active_sheet->getStyle($title_range)->getAlignment()->setWrapText(true);
		
		//General info
		$generalInfoStyleArray = array(
				'font' => array(
						'bold' => true,
						'size' => 11,
				),
		);
		
		$active_sheet->setCellValue('A9', "管理団体　　：\n受け入れ企業：")
		->mergeCells("A9:C9")
		->getStyle("A9:C9")->applyFromArray($generalInfoStyleArray);
		$active_sheet->getRowDimension(9)->setRowHeight(42);
		$active_sheet->getStyle("A9:C9")->getAlignment()->setWrapText(true);
		
		$active_sheet->setCellValue('D9', "面接日　：". date(LGConstant::DATE_BIRTH_FORMAT_FOR_DATABASE). "\n入国予定：　".count($requestor_list) ."月")
		->mergeCells("D9:E9")
		->getStyle("D9:E9")->applyFromArray($generalInfoStyleArray)->getAlignment()->setWrapText(true);
		$active_sheet->getStyle("D9:E9")->getAlignment()->setWrapText(true);
		
		$active_sheet->setCellValue('F9', "職種：")
		->mergeCells("F9:G9")
		->getStyle("F9:G9")->applyFromArray($generalInfoStyleArray)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$active_sheet->getStyle("F9:G9")->getAlignment()->setWrapText(true);
		
		//Grid
		$row_index+=10;
		//Grid header
		$headerStyleArray = array(
				'font' => array(
						'bold' => true,
						'size' => 12,
						'color' => array('rgb' => 'FFFFFF'),
				),
				'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,
						'color' => array('rgb' => '004477')),
				'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'wrap'=>true),
				'borders' => array('allborders' => array(
						'style' => PHPExcel_Style_Border::BORDER_THIN,
				),
				)
		);
		
		$header_range = sprintf('A%d:H%d', $row_index, $row_index);
		$active_sheet->setCellValue('A' . $row_index, '番号')
		->setCellValue('B' . $row_index, t('LBL_FULL_NAME_JP', 'profile'))
		->setCellValue('C' . $row_index, t('LBL_PHOTO_URL_JP', 'profile'))
		->setCellValue('D' . $row_index, t('LBL_BIRTH_PLACE_JP', 'profile'))
		->setCellValue('E' . $row_index, t('LBL_AGE_JP', 'profile'))
		->setCellValue('F' . $row_index, t('LBL_HEIGHT_JP', 'profile') . '/' . t('LBL_WEIGHT_JP', 'profile'))
		->setCellValue('G' . $row_index, t('LBL_CERTIFICATE_JP', 'academy'))
		->setCellValue('H' . $row_index, '備考')
		->getStyle($header_range)->applyFromArray($headerStyleArray);
		
		$row_index++;
		$contentStyles = array(
				'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'wrap' => true),
				'borders' => array('allborders' => array(
						'style' => PHPExcel_Style_Border::BORDER_THIN,
				),
				),
		);
		$contentLeftStyles = array(
				'alignment' => array('wrap' => true),
				'borders' => array('allborders' => array(
						'style' => PHPExcel_Style_Border::BORDER_THIN,
				),
				),
		);
		
		
		$index = 1;
		$start_index = $row_index;
		foreach ($requestor_list as $requestor) {
			set_time_limit(30);
			$active_sheet->setCellValue('A' . $row_index, $index);
			if($requestor->profileJP != null){
				$active_sheet->setCellValue('B' . $row_index, $requestor->profileJP[0]->full_name . "\n" . $requestor->full_name);
				$active_sheet->setCellValue('G' . $row_index, $requestor->profileJP[0]->academy->certificate);
			}
			else{
				$active_sheet->setCellValue('B' . $row_index, $requestor->full_name);
				$active_sheet->setCellValue('G' . $row_index, $requestor->academy->certificate);
			}
			if($requestor->photo_url != null && file_exists(PROFILE_FOLDER . $requestor->photo_url)){
				#Insert image into cell
				$objDrawing = new PHPExcel_Worksheet_Drawing();
				$objDrawing->setWorksheet($active_sheet);
				$objDrawing->setName("profile_image");
				$objDrawing->setDescription("photo of candidate");
				$objDrawing->setPath(PROFILE_FOLDER . $requestor->photo_url);
				$objDrawing->setCoordinates('C' . $row_index);
				$objDrawing->setResizeProportional(false);
				$objDrawing->setHeight(78);
				$objDrawing->setWidth(82);
				
				$objDrawing->setOffsetX(5);
				$objDrawing->setOffsetY(5);
				$active_sheet->getRowDimension($row_index)->setRowHeight(65);
			}else{
				$active_sheet->setCellValue('C' . $row_index, $requestor->photo_url);
			}
			
			$active_sheet->setCellValue('D' . $row_index, $requestor->province->name_jp);
			$active_sheet->setCellValue('E' . $row_index, $requestor->age);
			$active_sheet->setCellValue('F' . $row_index, $requestor->height . '/ ' . $requestor->weight);
			$active_sheet->setCellValue('H' . $row_index, '');
			$row_index++;
			$index++;
		}
		$end_index = $row_index - 1;
		$range_template = '%s' . $start_index . ':%s' . $end_index;
		
		//Apply grid style
		$active_sheet->getStyle(sprintf($range_template, 'A', 'A'))->applyFromArray($contentStyles);
		$active_sheet->getStyle(sprintf($range_template, 'B', 'B'))->applyFromArray($contentLeftStyles);
		$active_sheet->getStyle(sprintf($range_template, 'C', 'C'))->applyFromArray($contentStyles);
		$active_sheet->getStyle(sprintf($range_template, 'D', 'D'))->applyFromArray($contentStyles);
		$active_sheet->getStyle(sprintf($range_template, 'E', 'E'))->applyFromArray($contentStyles);
		$active_sheet->getStyle(sprintf($range_template, 'F', 'F'))->applyFromArray($contentStyles);
		$active_sheet->getStyle(sprintf($range_template, 'G', 'G'))->applyFromArray($contentStyles);
		$active_sheet->getStyle(sprintf($range_template, 'H', 'H'))->applyFromArray($contentStyles);
		
		//Signature
		$signatureStyleArray = array(
				'font' => array(
						'bold' => true,
						'size' => 14,
				),
		);
		
		$active_sheet->setCellValue('B'.$row_index, "VINACONEX SAIGON 株式会社")
		->getStyle('B'.$row_index)->applyFromArray($signatureStyleArray);
		$active_sheet->getRowDimension($row_index)->setRowHeight(42);
		
		
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header('Content-Disposition: attachment; filename = "Du Lieu Phong Van ' . date(LGConstant::DATE_FORMAT_NOT_TIME_MONTH_NUMBER) . '.xlsx"');
		header('Cache-Control: max-age = 0');
		
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		$objWriter->save('php://output');
	}
	
	private function get_changed_attributes($oldProfile){
		$keep_track_fields = array('full_name', 'address', 'father_name', 'father_job', 'mother_name', 'mother_job',
		'favorite', 'sport', 'advantage','disadvantage', 'purpose_of_go_abroad', 'skill_to_improve', 'after_go_abroad',
		'has_friend_and_relative', 'best_subject', 'major', 'certificate', 'language'
		);
		#Keep track updated fields
		$changed_fields = array();
		if($oldProfile->updated_fields != NULL)
			$changed_fields = explode(',', $oldProfile->updated_fields);
		
		//Basic info
		foreach($_POST[ProfileController::MODEL_CLASS_NAME] as $i => $attProfile){
			if($attProfile != $oldProfile->attributes[$i] && in_array($i, $keep_track_fields) && in_array($i, $changed_fields) == FALSE){
				array_push($changed_fields, $i);
			}
		}	
		//Family info
		foreach($_POST['Family'] as $j => $attProfile){
			if($attProfile != $oldProfile->family->attributes[$j] && in_array($j, $keep_track_fields) && in_array($j, $changed_fields) == FALSE){
				array_push($changed_fields, $j);
			}
		}
		
		//Academy info
		foreach($_POST['Academy'] as $k => $attProfile){
			if($attProfile != $oldProfile->academy->attributes[$k] && in_array($k, $keep_track_fields) && in_array($k, $changed_fields) == FALSE){
				array_push($changed_fields, $k);
			}
		}
		
		if (count($changed_fields) > 0)
    		return implode(',', $changed_fields);
    	else
    		return NULL;
	}
	
	private function get_changed_studyingprocess_attributes($studyingProcess, $postData){
		#Keep track updated fields
		$changed_fields = array();
		if($studyingProcess->updated_fields != NULL)
			$changed_fields = explode(',', $studyingProcess->updated_fields);
		
		//Basic info
		foreach($postData as $i => $att){
			if($att != $studyingProcess->attributes[$i] && in_array($i, $changed_fields) == FALSE){
				array_push($changed_fields, $i);
			}
		}
		
		if (count($changed_fields) > 0)
			return implode(',', $changed_fields);
		else
			return NULL;
	}
	
	private function get_changed_workingprocess_attributes($workingProcess, $postData){
		#Keep track updated fields
		$changed_fields = array();
		if($workingProcess->updated_fields != NULL)
			$changed_fields = explode(',', $workingProcess->updated_fields);
	
		//Basic info
		foreach($postData as $i => $att){
			if($att != $workingProcess->attributes[$i] && in_array($i, $changed_fields) == FALSE){
				array_push($changed_fields, $i);
			}
		}
	
		if (count($changed_fields) > 0)
			return implode(',', $changed_fields);
		else
			return NULL;
	}
	
	public function actionStatus(){
		$model = $this->loadModel();
		$s = $_GET["s"];
		//Must throw CHttpException, If not, when ajax request, error will not run errorHandler
		if ($model == NULL || count($model) == 0) {
			//Must return 200 to client recieve this error
			throw new CHttpException(200, t('MSG_ERROR_CHANGE_STATUS_REQUIRED_FIELD', 'messages'));
		}
		foreach($model as $id => $item){
			$item->status = $s;
			$item->saveAttributes(array('status'));
		}
		$this->showMessage(MsgType::SUCCESS, t('MSG_INFO_UPDATE_SUCCESS', 'messages', array('{0}' => t('LBL_PROFILE','profile'))));
		$this->redirect(array('index'));
	}
	
	public function actionGetprofilebycode(){
		$model = null;
		if (isset($_GET['code'])) {
			$code = $_GET['code']; 
			if (isset($code)) {
				$criteria = new CDbCriteria;
				$criteria->compare('code', $code);
				$model = Profile::model()->with('advanceProfile')->findAll($criteria);;
			}
		}
		if (isset($model) && count($model) > 0){
			$model = $model[0];
			$redirectUrl = '';
			$logedInUser = User::model()->findByPk(Yii::app()->user->id);
			//if(in_array($data->status, array(ProfileStatus::STATUS_TRANSLATED, ProfileStatus::STATUS_ACCEPTED, ProfileStatus::STATUS_WAITING_FOR_RETRANSLATE,ProfileStatus::STATUS_TRANSLATING,ProfileStatus::STATUS_PASS,ProfileStatus::STATUS_FLY, ProfileStatus::STATUS_STAY, ProfileStatus::STATUS_COMPLETE, ProfileStatus::STATUS_OUT)) && ($logedInUser->isManager() || $logedInUser->isEditor() || $logedInUser->isAdmin() || $logedInUser->isSuperAdmin())){
			if(!$logedInUser->isSubmitter())
			{
				$redirectUrl = Yii::app()->createUrl("translate/detail", array("id" => $model->id,"alias"=>stringURLSafe($model->full_name)));
			}else{
				$redirectUrl = Yii::app()->createUrl("profile/detail", array("id" => $model->id,"alias"=>stringURLSafe($model->full_name)));
			}
			echo CJSON::encode(array('status' => 'success', 'redirectUrl' => $redirectUrl));
			Yii::app()->end();
		}else{
			echo CJSON::encode(array('status' => 'error', 'message' => t('MSG_NOT_FOUND_PROFILE', 'messages')));
			Yii::app()->end();
		}
	}
	
	public function actionExportall() {
		//Prepare data
		$row_index = 1;
		$model = new Profile(Profile::SCENARIO_EXPORT);
		$model->academy = new Academy();
		$model->province = new Province();
		$ids = $_GET['id'];
		$requestor_list = $model->exportAll($ids);
		$total_requestor = count($requestor_list);
	
		ini_set("memory_limit", "-1");
		/* Fill data to Excel */
		Yii::import('ext.PHPExcel.**', true);
		$objPHPExcel = new PHPExcel();
		//Set default style
		$default_style = array(
				'font' => array(
						'name' => 'Calibri',
						'size' => 11,
						'color' => array('rgb' => '333333'),
				),
				//'alignment' => array('vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER),
		);
		$objPHPExcel->getDefaultStyle()->applyFromArray($default_style);
		$objPHPExcel->setActiveSheetIndex(0);
		$active_sheet = $objPHPExcel->getActiveSheet();
	
		//page setup
		$active_sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
		$active_sheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
		$active_sheet->getPageSetup()->setFitToPage(true);
		$active_sheet->getPageSetup()->setFitToWidth(1);
		$active_sheet->getPageSetup()->setFitToHeight(0);
		$pageMargins = $active_sheet->getPageMargins();
		// margin is set in inches (0.5cm)
		$margin = 1 / 2.54;
		$pageMargins->setTop($margin);
		$pageMargins->setBottom($margin);
		$pageMargins->setLeft($margin);
		$pageMargins->setRight($margin);
	
		$active_sheet->setTitle(t('EXPORT_ALL_SHEET_TITLE', 'profile'))
		->setShowGridlines(false);
		$active_sheet->getColumnDimension("A")->setWidth(14);
		$active_sheet->getColumnDimension("B")->setWidth(10);
		$active_sheet->getColumnDimension("C")->setWidth(15);
		$active_sheet->getColumnDimension("D")->setWidth(10);
		$active_sheet->getColumnDimension("E")->setWidth(10);
		$active_sheet->getColumnDimension("F")->setWidth(15);
		$active_sheet->getColumnDimension("G")->setWidth(20);
		$active_sheet->getColumnDimension("H")->setWidth(20);
		$active_sheet->getColumnDimension("I")->setWidth(30);
		$active_sheet->getColumnDimension("J")->setWidth(15);
		$active_sheet->getColumnDimension("K")->setWidth(15);
		$active_sheet->getColumnDimension("L")->setWidth(20);
		$active_sheet->getColumnDimension("M")->setWidth(15);
		$active_sheet->getColumnDimension("N")->setWidth(15);
		$active_sheet->getColumnDimension("O")->setWidth(20);
		$active_sheet->getColumnDimension("P")->setWidth(20);
		$active_sheet->getColumnDimension("Q")->setWidth(20);
		$active_sheet->getColumnDimension("R")->setWidth(15);
		$active_sheet->getColumnDimension("S")->setWidth(15);
		$active_sheet->getColumnDimension("T")->setWidth(15);
		$active_sheet->getColumnDimension("U")->setWidth(15);
		$active_sheet->getColumnDimension("V")->setWidth(20);
		$active_sheet->getColumnDimension("W")->setWidth(20);
		$active_sheet->getColumnDimension("X")->setWidth(20);
		$active_sheet->getColumnDimension("Y")->setWidth(20);
		$active_sheet->getColumnDimension("Z")->setWidth(15);
		$active_sheet->getColumnDimension("AA")->setWidth(30);
		$active_sheet->getColumnDimension("AB")->setWidth(30);
		$active_sheet->getColumnDimension("AC")->setWidth(40);
		$active_sheet->getColumnDimension("AD")->setWidth(20);
		$active_sheet->getColumnDimension("AE")->setWidth(30);
		$active_sheet->getColumnDimension("AF")->setWidth(40);
		$active_sheet->getColumnDimension("AG")->setWidth(15);
		$active_sheet->getColumnDimension("AH")->setWidth(20);
		$active_sheet->getColumnDimension("AI")->setWidth(15);
		$active_sheet->getColumnDimension("AJ")->setWidth(15);
		$active_sheet->getColumnDimension("AK")->setWidth(15);
		$active_sheet->getColumnDimension("AL")->setWidth(15);
		$active_sheet->getColumnDimension("AM")->setWidth(40);
		$active_sheet->getColumnDimension("AN")->setWidth(20);
		$active_sheet->getColumnDimension("AO")->setWidth(20);
		$active_sheet->getColumnDimension("AP")->setWidth(20);
		$active_sheet->getColumnDimension("AQ")->setWidth(30);
		$active_sheet->getColumnDimension("AR")->setWidth(40);
		$active_sheet->getColumnDimension("AS")->setWidth(20);
		$active_sheet->getColumnDimension("AT")->setWidth(20);
		$active_sheet->getColumnDimension("AU")->setWidth(20);
		$active_sheet->getColumnDimension("AV")->setWidth(20);
		
		// title
		$titleStyleArray = array (
				'font' => array (
						'bold' => true,
						'size' => 16,
				),
		);
	
		$title_range = "B2";
		$active_sheet->setCellValue ( 'B2', "THÔNG TIN TỔNG HỢP NGƯỜI LAO ĐỘNG ĐI LÀM VIỆC TẠI NGƯỚC NGOÀI ( VINACONEX )" )->getStyle ( $title_range )->applyFromArray ( $titleStyleArray );
			
		// Grid
		$row_index += 3;
		// Grid header
		$headerStyleArray = array (
				'font' => array (
						'bold' => true,
						'size' => 12,
						'color' => array (
								'rgb' => 'FFFFFF' 
						) 
				),
				'fill' => array (
						'type' => PHPExcel_Style_Fill::FILL_SOLID,
						'color' => array (
								'rgb' => '004477' 
						) 
				),
				'alignment' => array (
						'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
						'wrap' => true 
				),
				'borders' => array (
						'allborders' => array (
								'style' => PHPExcel_Style_Border::BORDER_THIN 
						) 
				) 
		);
	
		$header_range = sprintf('A%d:AV%d', $row_index, $row_index);
		$active_sheet->setCellValue('B' . $row_index, t('EXPORT_HEADER_CODE', 'profile'))
					->setCellValue('C' . $row_index, t('EXPORT_HEADER_LAST_NAME', 'profile'))
					->setCellValue('D' . $row_index, t('EXPORT_HEADER_MIDDLE_NAME', 'profile'))
					->setCellValue('E' . $row_index, t('EXPORT_HEADER_FIRST_NAME', 'profile'))
					->setCellValue('F' . $row_index, t('EXPORT_HEADER_SEX', 'profile'))
					->setCellValue('G' . $row_index, t('EXPORT_HEADER_BIRTH_YEAR', 'profile'))
					->setCellValue('H' . $row_index, t('EXPORT_HEADER_BIRTH_PLACE', 'profile'))
					->setCellValue('I' . $row_index, t('EXPORT_HEADER_PHONE_NUMBER', 'profile'))
					->setCellValue('J' . $row_index, t('EXPORT_HEADER_ADDRESS', 'profile'))
					->setCellValue('K' . $row_index, t('EXPORT_HEADER_ID_CARD', 'profile'))
					->setCellValue('L' . $row_index, t('EXPORT_HEADER_ID_CARD_PROVIDED_DATE', 'profile'))
					->setCellValue('M' . $row_index, t('EXPORT_HEADER_ID_CARD_PROVIDED_PLACE', 'profile'))
					->setCellValue('N' . $row_index, t('EXPORT_HEADER_PASSPORT', 'profile'))
					->setCellValue('O' . $row_index, t('EXPORT_HEADER_PASSPORT_PROVIDED_DATE', 'profile'))
					->setCellValue('P' . $row_index, t('EXPORT_HEADER_PASSPORT_PROVIDED_PLACE', 'profile'))
					->setCellValue('Q' . $row_index, t('EXPORT_HEADER_FATHER_NAME', 'profile'))
					->setCellValue('R' . $row_index, t('EXPORT_HEADER_MOTHER_NAME', 'profile'))
					->setCellValue('S' . $row_index, t('EXPORT_HEADER_BLOOD_GROUP', 'profile'))
					->setCellValue('T' . $row_index, t('EXPORT_HEADER_HEIGHT', 'profile'))
					->setCellValue('U' . $row_index, t('EXPORT_HEADER_WEIGHT', 'profile'))
					->setCellValue('V' . $row_index, t('EXPORT_HEADER_HEALTH_NOTE', 'profile'))
					->setCellValue('W' . $row_index, t('EXPORT_HEADER_HEALTH_CHECKING_DATE', 'profile'))
					->setCellValue('X' . $row_index, t('EXPORT_HEADER_APPLY_DATE', 'profile'))
					->setCellValue('Y' . $row_index, t('EXPORT_HEADER_SELECTED_DATE', 'profile'))
					->setCellValue('Z' . $row_index, t('EXPORT_HEADER_ORDER', 'profile'))
					->setCellValue('AA' . $row_index, t('EXPORT_HEADER_CONTRACT_DURATION', 'profile'))
					->setCellValue('AC' . $row_index, t('EXPORT_HEADER_HIRING_COMPANY', 'profile'))
					->setCellValue('AD' . $row_index, t('EXPORT_HEADER_HIRING_COMPANY_ADDRESS', 'profile'))
					->setCellValue('AE' . $row_index, t('EXPORT_HEADER_HIRING_COMPANY_DIRECTOR', 'profile'))
					->setCellValue('AF' . $row_index, t('EXPORT_HEADER_UNION', 'profile'))
					->setCellValue('AG' . $row_index, t('EXPORT_HEADER_UNION_ADDRESS', 'profile'))
					->setCellValue('AH' . $row_index, t('EXPORT_HEADER_UNION_PHONE_NUMBER', 'profile'))
					->setCellValue('AI' . $row_index, t('EXPORT_HEADER_UNION_PRESIDENT', 'profile'))
					->setCellValue('AJ' . $row_index, t('EXPORT_HEADER_SALARY', 'profile'))
					->setCellValue('AK' . $row_index, t('EXPORT_HEADER_ALLOWANCE', 'profile'))
					->setCellValue('AL' . $row_index, t('EXPORT_HEADER_INTRODUCING_FEE', 'profile'))
					->setCellValue('AM' . $row_index, t('EXPORT_HEADER_GO_DATE', 'profile'))
					->setCellValue('AN' . $row_index, t('EXPORT_HEADER_ADDRESS_VISA', 'profile'))
					->setCellValue('AO' . $row_index, t('EXPORT_HEADER_ARRIVE_CITY', 'profile'))
					->setCellValue('AP' . $row_index, t('EXPORT_HEADER_FLIGHT', 'profile'))
					->setCellValue('AQ' . $row_index, t('EXPORT_INTRODUCING_COMPANY_CODE', 'profile'))
					->setCellValue('AR' . $row_index, t('EXPORT_INTRODUCING_COMPANY', 'profile'))
					->setCellValue('AS' . $row_index, t('EXPORT_INTRODUCING_COMPANY_ADDRESS', 'profile'))
					->setCellValue('AT' . $row_index, t('EXPORT_INTRODUCING_COMPANY_PRESENTATIVE', 'profile'))
					->setCellValue('AU' . $row_index, t('EXPORT_INTRODUCING_COMPANY_PHONE_NUMBER', 'profile'))
					->setCellValue('AV' . $row_index, t('EXPORT_CONTRACT_NUMBER', 'profile'))
		->getStyle($header_range)->applyFromArray($headerStyleArray);
	
		$row_index ++;
		$contentStyles = array (
				'alignment' => array (
						'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
						'wrap' => true 
				),
				'borders' => array (
						'allborders' => array (
								'style' => PHPExcel_Style_Border::BORDER_THIN 
						) 
				) 
		);
		$contentLeftStyles = array (
				'alignment' => array (
						'wrap' => true 
				),
				'borders' => array (
						'allborders' => array (
								'style' => PHPExcel_Style_Border::BORDER_THIN 
						) 
				) 
		);
	
		$index = 1;
			$start_index = $row_index;
			foreach ($requestor_list as $requestor) {
				if($requestor->photo_url != null && file_exists(PROFILE_FOLDER . $requestor->photo_url)){
					#Insert image into cell
					$objDrawing = new PHPExcel_Worksheet_Drawing();
					$objDrawing->setWorksheet($active_sheet);
					$objDrawing->setName("profile_image");
					$objDrawing->setDescription("photo of candidate");
					$objDrawing->setPath(PROFILE_FOLDER . $requestor->photo_url);
					$objDrawing->setCoordinates('A' . $row_index);
					$objDrawing->setResizeProportional(false);
					$objDrawing->setHeight(78);
					$objDrawing->setWidth(82);
				
					$objDrawing->setOffsetX(5);
					$objDrawing->setOffsetY(5);
					$active_sheet->getRowDimension($row_index)->setRowHeight(65);
				}else{
					$active_sheet->setCellValue('A' . $row_index, $requestor->photo_url);
				}
				
				//Vietnames record
				set_time_limit(30);
				$full_name = $requestor->full_name;
				$parts = explode(" ", $full_name);
				$first_name = $parts[count($parts) - 1];
				$last_name = $parts[0];
				$middle_name_parts = array_slice($parts, 1, count($parts) - 2);
				$middle_name = implode(' ', $middle_name_parts);
				$active_sheet->setCellValue('B' . $row_index, $full_name);
				$active_sheet->setCellValue('C' . $row_index, $last_name);
				$active_sheet->setCellValue('D' . $row_index, $middle_name);
				$active_sheet->setCellValue('E' . $row_index, $first_name);
				$active_sheet->setCellValue('F' . $row_index, ($requestor->sex == 1) ? 'Nam' : 'Nữ');
				$active_sheet->setCellValue('G' . $row_index, $requestor->birth_date);
				$active_sheet->setCellValue('H' . $row_index, $requestor->province->province_name);
				//Set format text for phone_number
				$active_sheet->getCell('I' . $row_index)->setValueExplicit($requestor->phone_number, PHPExcel_Cell_DataType::TYPE_STRING);
				$active_sheet->getStyle('I' . $row_index)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
				$active_sheet->setCellValue('J' . $row_index, $requestor->address);
				$active_sheet->getCell('K' . $row_index)->setValueExplicit($requestor->advanceProfile->id_card_number, PHPExcel_Cell_DataType::TYPE_STRING);
				$active_sheet->getStyle('K' . $row_index)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
				$active_sheet->setCellValue('L' . $row_index, $requestor->advanceProfile->id_card_provided_date);
				$active_sheet->setCellValue('M' . $row_index, $requestor->advanceProfile->id_card_provided_place);
				$active_sheet->getCell('N' . $row_index)->setValueExplicit($requestor->advanceProfile->passport_number, PHPExcel_Cell_DataType::TYPE_STRING);
				$active_sheet->getStyle('N' . $row_index)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
				$active_sheet->setCellValue('O' . $row_index, $requestor->advanceProfile->passport_provided_date);
				$active_sheet->setCellValue('P' . $row_index, $requestor->advanceProfile->passport_provided_place);
				$active_sheet->setCellValue('Q' . $row_index, $requestor->family->father_name);
				$active_sheet->setCellValue('R' . $row_index, $requestor->family->mother_name);
				$active_sheet->setCellValue('S' . $row_index, $requestor->blood_group);
				$active_sheet->setCellValue('T' . $row_index, $requestor->height);
				$active_sheet->setCellValue('U' . $row_index, $requestor->weight);
				$active_sheet->setCellValue('V' . $row_index, ($requestor->is_pass_health_checking == 1) ? 'Đạt' : 'Không đạt');
				$active_sheet->setCellValue('W' . $row_index, $requestor->health_checking_date);
				$active_sheet->setCellValue('X' . $row_index, $requestor->advanceProfile->apply_date);
				$active_sheet->setCellValue('Y' . $row_index, $requestor->advanceProfile->selected_date);
				$active_sheet->setCellValue('Z' . $row_index, $requestor->advanceProfile->order);
				$active_sheet->setCellValue('AA' . $row_index, $requestor->advanceProfile->contract_duration);
				$active_sheet->getCell('AB' . $row_index)->setValueExplicit($requestor->advanceProfile->hiringCompany->code, PHPExcel_Cell_DataType::TYPE_STRING);
				$active_sheet->getStyle('AB' . $row_index)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
				$active_sheet->setCellValue('AC' . $row_index, $requestor->advanceProfile->hiringCompany->name);
				$active_sheet->setCellValue('AD' . $row_index, $requestor->advanceProfile->hiringCompany->address);
				$active_sheet->setCellValue('AE' . $row_index, $requestor->advanceProfile->hiringCompany->director);
				$active_sheet->setCellValue('AF' . $row_index, $requestor->advanceProfile->hiringCompany->union->name);
				$active_sheet->setCellValue('AG' . $row_index, $requestor->advanceProfile->hiringCompany->union->address);
				$active_sheet->getCell('AH' . $row_index)->setValueExplicit($requestor->advanceProfile->hiringCompany->union->phone_number, PHPExcel_Cell_DataType::TYPE_STRING);
				$active_sheet->getStyle('AH' . $row_index)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
				$active_sheet->setCellValue('AI' . $row_index, $requestor->advanceProfile->hiringCompany->union->president);
				$active_sheet->setCellValue('AJ' . $row_index, $requestor->advanceProfile->salary);
				$active_sheet->setCellValue('AK' . $row_index, $requestor->advanceProfile->hiringCompany->first_month_allowance);
				$active_sheet->setCellValue('AL' . $row_index, $requestor->advanceProfile->introducing_fee);
				$active_sheet->setCellValue('AM' . $row_index, $requestor->advanceProfile->go_date);
				$active_sheet->setCellValue('AN' . $row_index, $requestor->advanceProfile->visa_address);
				$active_sheet->setCellValue('AO' . $row_index, $requestor->advanceProfile->arrive_city);
				$active_sheet->setCellValue('AP' . $row_index, $requestor->advanceProfile->flight);
				$active_sheet->getCell('AQ' . $row_index)->setValueExplicit($requestor->advanceProfile->introducingCompany->code, PHPExcel_Cell_DataType::TYPE_STRING);
				$active_sheet->getStyle('AQ' . $row_index)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
				$active_sheet->setCellValue('AR' . $row_index, $requestor->advanceProfile->introducingCompany->name);
				$active_sheet->setCellValue('AS' . $row_index, $requestor->advanceProfile->introducingCompany->address);
				$active_sheet->setCellValue('AT' . $row_index, $requestor->advanceProfile->introducingCompany->representative);
				$active_sheet->getCell('AU' . $row_index)->setValueExplicit($requestor->advanceProfile->introducingCompany->phone_number, PHPExcel_Cell_DataType::TYPE_STRING);
				$active_sheet->getStyle('AU' . $row_index)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
				$active_sheet->getCell('AV' . $row_index)->setValueExplicit($requestor->advanceProfile->contract_number, PHPExcel_Cell_DataType::TYPE_STRING);
				$active_sheet->getStyle('AV' . $row_index)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
				
				//Japanese record
				if(count($requestor->profileJP)>0){
					set_time_limit(30);
					$full_name = $requestor->profileJP[0]->full_name;
					$parts = explode(" ", $full_name);
					$first_name = $parts[count($parts) - 1];
					$last_name = $parts[0];
					$middle_name_parts = array_slice($parts, 1, count($parts) - 2);
					$middle_name = implode(' ', $middle_name_parts);
					$active_sheet->setCellValue('B' . ($row_index + 1), $full_name);
					$active_sheet->setCellValue('C' . ($row_index + 1), $last_name);
					$active_sheet->setCellValue('D' . ($row_index + 1), $middle_name);
					$active_sheet->setCellValue('E' . ($row_index + 1), $first_name);
					$active_sheet->setCellValue('F' . ($row_index + 1), ($requestor->sex == 1) ? 'Nam' : 'Nữ');
					$active_sheet->setCellValue('G' . ($row_index + 1), $requestor->birth_date);
					$active_sheet->setCellValue('H' . ($row_index + 1), $requestor->province->name_jp);
					//Set format text for phone_number
					$active_sheet->getCell('I' . ($row_index + 1))->setValueExplicit($requestor->phone_number, PHPExcel_Cell_DataType::TYPE_STRING);
					$active_sheet->getStyle('I' . ($row_index + 1))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
					$active_sheet->setCellValue('J' . ($row_index + 1), $requestor->profileJP[0]->address);
					$active_sheet->getCell('K' . ($row_index + 1))->setValueExplicit($requestor->advanceProfile->id_card_number, PHPExcel_Cell_DataType::TYPE_STRING);
					$active_sheet->getStyle('K' . ($row_index + 1))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
					$active_sheet->setCellValue('L' . ($row_index + 1), $requestor->advanceProfile->id_card_provided_date);
					$active_sheet->setCellValue('M' . ($row_index + 1), $requestor->advanceProfile->id_card_provided_place);
					$active_sheet->getCell('N' . ($row_index + 1))->setValueExplicit($requestor->advanceProfile->passport_number, PHPExcel_Cell_DataType::TYPE_STRING);
					$active_sheet->getStyle('N' . ($row_index + 1))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
					$active_sheet->setCellValue('O' . ($row_index + 1), $requestor->advanceProfile->passport_provided_date);
					$active_sheet->setCellValue('P' . ($row_index + 1), $requestor->advanceProfile->passport_provided_place);
					$active_sheet->setCellValue('Q' . ($row_index + 1), $requestor->profileJP[0]->family->father_name);
					$active_sheet->setCellValue('R' . ($row_index + 1), $requestor->profileJP[0]->family->mother_name);
					$active_sheet->setCellValue('S' . ($row_index + 1), $requestor->blood_group);
					$active_sheet->setCellValue('T' . ($row_index + 1), $requestor->height);
					$active_sheet->setCellValue('U' . ($row_index + 1), $requestor->weight);
					$active_sheet->setCellValue('V' . ($row_index + 1), ($requestor->is_pass_health_checking == 1) ? 'Đạt' : 'Không đạt');
					$active_sheet->setCellValue('W' . ($row_index + 1), $requestor->health_checking_date);
					$active_sheet->setCellValue('X' . ($row_index + 1), $requestor->advanceProfile->apply_date);
					$active_sheet->setCellValue('Y' . ($row_index + 1), $requestor->advanceProfile->selected_date);
					$active_sheet->setCellValue('Z' . ($row_index + 1), $requestor->advanceProfile->order);
					$active_sheet->setCellValue('AA' . ($row_index + 1), $requestor->advanceProfile->contract_duration);
					$active_sheet->getCell('AB' . ($row_index + 1))->setValueExplicit($requestor->advanceProfile->hiringCompany->code, PHPExcel_Cell_DataType::TYPE_STRING);
					$active_sheet->getStyle('AB' . ($row_index + 1))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
					$active_sheet->setCellValue('AC' . ($row_index + 1), $requestor->advanceProfile->hiringCompany->name);
					$active_sheet->setCellValue('AD' . ($row_index + 1), $requestor->advanceProfile->hiringCompany->address);
					$active_sheet->setCellValue('AE' . ($row_index + 1), $requestor->advanceProfile->hiringCompany->director);
					$active_sheet->setCellValue('AF' . ($row_index + 1), $requestor->advanceProfile->hiringCompany->union->name);
					$active_sheet->setCellValue('AG' . ($row_index + 1), $requestor->advanceProfile->hiringCompany->union->address);
					$active_sheet->getCell('AH' . ($row_index + 1))->setValueExplicit($requestor->advanceProfile->hiringCompany->union->phone_number, PHPExcel_Cell_DataType::TYPE_STRING);
					$active_sheet->getStyle('AH' . ($row_index + 1))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
					$active_sheet->setCellValue('AI' . ($row_index + 1), $requestor->advanceProfile->hiringCompany->union->president);
					$active_sheet->setCellValue('AJ' . ($row_index + 1), $requestor->advanceProfile->salary);
					$active_sheet->setCellValue('AK' . ($row_index + 1), $requestor->advanceProfile->hiringCompany->first_month_allowance);
					$active_sheet->setCellValue('AL' . ($row_index + 1), $requestor->advanceProfile->introducing_fee);
					$active_sheet->setCellValue('AM' . ($row_index + 1), $requestor->advanceProfile->go_date);
					$active_sheet->setCellValue('AN' . ($row_index + 1), $requestor->advanceProfile->visa_address);
					$active_sheet->setCellValue('AO' . ($row_index + 1), $requestor->advanceProfile->arrive_city);
					$active_sheet->setCellValue('AP' . ($row_index + 1), $requestor->advanceProfile->flight);
					$active_sheet->getCell('AQ' . ($row_index + 1))->setValueExplicit($requestor->advanceProfile->introducingCompany->code, PHPExcel_Cell_DataType::TYPE_STRING);
					$active_sheet->getStyle('AQ' . ($row_index + 1))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
					$active_sheet->setCellValue('AR' . ($row_index + 1), $requestor->advanceProfile->introducingCompany->name);
					$active_sheet->setCellValue('AS' . ($row_index + 1), $requestor->advanceProfile->introducingCompany->address);
					$active_sheet->setCellValue('AT' . ($row_index + 1), $requestor->advanceProfile->introducingCompany->representative);
					$active_sheet->getCell('AU' . ($row_index + 1))->setValueExplicit($requestor->advanceProfile->introducingCompany->phone_number, PHPExcel_Cell_DataType::TYPE_STRING);
					$active_sheet->getStyle('AU' . ($row_index + 1))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
					$active_sheet->getCell('AV' . ($row_index + 1))->setValueExplicit($requestor->advanceProfile->contract_number, PHPExcel_Cell_DataType::TYPE_STRING);
					$active_sheet->getStyle('AV' . ($row_index + 1))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
				}
				
				$row_index = $row_index + 2;
				$index = $index + 2;
			}
			$end_index = $row_index - 1;
			$range_template = '%s' . $start_index . ':%s' . $end_index;
	
			//Apply grid style
			$active_sheet->getStyle(sprintf($range_template, 'A', 'A'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'B', 'B'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'C', 'C'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'D', 'D'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'E', 'E'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'F', 'F'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'G', 'G'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'H', 'H'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'I', 'I'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'J', 'J'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'K', 'K'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'L', 'L'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'M', 'M'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'N', 'N'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'O', 'o'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'P', 'P'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'Q', 'Q'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'R', 'R'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'S', 'S'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'T', 'T'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'U', 'U'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'V', 'V'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'W', 'W'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'X', 'X'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'Y', 'Y'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'Z', 'Z'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AA', 'AA'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AB', 'AB'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AC', 'AC'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AD', 'AD'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AE', 'AE'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AF', 'AF'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AG', 'AG'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AH', 'AH'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AI', 'AI'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AJ', 'AJ'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AK', 'AK'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AL', 'AL'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AM', 'AM'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AN', 'AN'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AO', 'AO'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AP', 'AP'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AQ', 'AQ'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AR', 'AR'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AS', 'AS'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AT', 'AT'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AU', 'AU'))->applyFromArray($contentStyles);
			$active_sheet->getStyle(sprintf($range_template, 'AV', 'AV'))->applyFromArray($contentStyles);
			
		header ( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
		header ( 'Content-Disposition: attachment; filename = "Du_Lieu_' . date ( LGConstant::DATE_FORMAT_NOT_TIME_MONTH_NUMBER ) . '.xlsx"' );
		header ( 'Cache-Control: max-age = 0' );
	
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		$objWriter->save('php://output');
	}
}

?>